//-----------------------------------------------------------------------
// <copyright file="SqlRoleAddUserTests.cs" company="Microsoft">
// Copyright (c) Microsoft Corporation.  All rights reserved.
// THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY 
// KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
// IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
// PARTICULAR PURPOSE.
// </copyright>
// <author>Barry Dorrans</author>
// <email>barryd@charteris.com</email>
// <date>2004-06-04</date>
// <summary>Unit test cases for the SqlServer AddUser action.</summary>
//-----------------------------------------------------------------------

namespace Microsoft.Sdc.Tasks.Test
{
    using System;
    using System.IO;
    using Microsoft.VisualStudio.TestTools.UnitTesting;
    using Microsoft.Sdc.Tasks.Configuration;
    using Microsoft.Sdc.Tasks;
    using Microsoft.Build.Framework;


    // We need these to create suitable users to add into SQL
    using Microsoft.Sdc.Tasks.Configuration.ActiveDirectory;
    using Microsoft.Sdc.Tasks.ActiveDirectory;

    // And we need these to impersonate users without adequate permissions in SQL to add people
    using System.Runtime.InteropServices;
    using System.Security.Principal;
    using System.Security.Permissions;

    // And we need these to check our results
    using System.Data;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;

    /// <summary>
    /// Unit tests for Tasks.Sql.Role.AddUser scenarios
    /// </summary>
    [TestClass]
    public class SqlRoleAddUserTests
    {
        private string _testUserName = String.Empty;
        private string _testPassword = "1234$abcd";
        private string _testRole = String.Empty;

        [TestInitialize]
        public void InitBuildEngineInterface()
        {
           this.CreateUserName();
        }

        /// <summary>
        /// Create a new, random(ish) local NT account for testing
        /// </summary>
        /// <remarks>
        /// Note that this relies on the ActiveDirectory.User.Create task functioning
        /// as expected.
        /// </remarks>
        private void CreateUserName()
        {
            Guid guid = Guid.NewGuid();
            string username = guid.ToString();
            username = username.Replace("-", "").Substring(0, 11);

            Console.WriteLine("Test Setup, creating test username " + username);

            if (User.Exists(username))
                Assert.Fail("Temporary user already exists.");
            // Now create the user, using the CreateActiveDirectoryUser task
            Tasks.ActiveDirectory.User.Create adTask = new Tasks.ActiveDirectory.User.Create();
            adTask.UserName = username;
            adTask.Password = _testPassword;
            bool taskReturnValue = adTask.Execute();
            Assert.IsTrue(taskReturnValue, "Cannot create temporary user.");
            // Check the user now exists
            bool userExists = User.Exists(username);
            Assert.IsTrue(userExists, "Temporary user existance check.");
            
            this._testUserName = username;

            // Now grant this user login rights to SQL
            SqlConnection dbConnection = new SqlConnection("Data Source=(local);" +
                            "Initial Catalog=master;" +
                            "Integrated Security=SSPI");
            dbConnection.Open();
            SqlCommand grantLogin = new SqlCommand("sp_grantlogin '" + 
                System.Environment.MachineName + @"\"+ this._testUserName + "'",
                dbConnection);
            SqlParameter grantSQLResult = grantLogin.Parameters.Add("@ReturnValue", SqlDbType.Int);
            grantSQLResult.Direction = ParameterDirection.ReturnValue;
            grantLogin.ExecuteNonQuery();
            int grantResult = (int) grantLogin.Parameters["@ReturnValue"].Value;
            grantLogin.Dispose();

            // And add it into the database
            SqlCommand grantDBAccess = new SqlCommand("sp_grantdbaccess '" +
                System.Environment.MachineName + @"\" + this._testUserName + "','"+
                System.Environment.MachineName + @"\" + this._testUserName + "'",
                dbConnection);
            SqlParameter grantDBResult = grantDBAccess.Parameters.Add("@ReturnValue", SqlDbType.Int);
            grantDBResult.Direction = ParameterDirection.ReturnValue;
            grantDBAccess.ExecuteNonQuery();
            int grantAccessResult = (int) grantDBAccess.Parameters["@ReturnValue"].Value;
            grantDBAccess.Dispose();


            // Now add a temporary role
            guid = Guid.NewGuid();
            string role = guid.ToString();
            this._testRole = role.Replace("-", "").Substring(0, 11);

            SqlCommand addRole = new SqlCommand("sp_addrole '" + this._testRole + "'",
                dbConnection);
            SqlParameter addRoleResult = addRole.Parameters.Add("@ReturnValue", SqlDbType.Int);
            addRoleResult.Direction = ParameterDirection.ReturnValue;
            addRole.ExecuteNonQuery();
            int roleResult = (int) addRole.Parameters["@ReturnValue"].Value;

            addRole.Dispose();
            dbConnection.Close();
            dbConnection.Dispose();

            Assert.AreEqual( 0, grantResult);
            Assert.AreEqual( 0, grantAccessResult);
            Assert.AreEqual( 0, roleResult);
        }

        /// <summary>
        /// Clean up the previously created testing username and role
        /// </summary>
        /// <remarks>
        /// As this has the [TearDown] attribute, nunit will run it at the end of each test
        /// </remarks>
        [TestCleanup]
        public void CleanUserName()
        {
            // Delete the temporary user from the temporary role
            SqlConnection dbConnection = new SqlConnection("Data Source=(local);" +
                            "Initial Catalog=master;" +
                            "Integrated Security=SSPI");
            dbConnection.Open();
            SqlCommand dropRoleMember = new SqlCommand("sp_droprolemember '" +
                this._testRole + "','" +
                System.Environment.MachineName + @"\"+this._testUserName+"'",
                dbConnection);
            SqlParameter dropMemberValue = dropRoleMember.Parameters.Add("@ReturnValue", SqlDbType.Int);
            dropMemberValue.Direction = ParameterDirection.ReturnValue;
            dropRoleMember.ExecuteNonQuery();
            int dropMemberResult = (int) dropRoleMember.Parameters["@ReturnValue"].Value;

            // Delete the temporary role we created
            SqlCommand dropRole = new SqlCommand("sp_droprole '" + this._testRole + "'",
                dbConnection);
            SqlParameter dropReturnValue = dropRole.Parameters.Add("@ReturnValue", SqlDbType.Int);
            dropReturnValue.Direction = ParameterDirection.ReturnValue;
            dropRole.ExecuteNonQuery();
            int dropRoleResult = (int) dropRole.Parameters["@ReturnValue"].Value;

            // Revoke the temporary username's access to SQL
            SqlCommand revokeLogin = new SqlCommand("sp_revokelogin '" + 
                System.Environment.MachineName + @"\"+ this._testUserName + "'",
                dbConnection);
            SqlParameter revokeReturnValue = revokeLogin.Parameters.Add("@ReturnValue", SqlDbType.Int);
            revokeReturnValue.Direction = ParameterDirection.ReturnValue;
            revokeLogin.ExecuteNonQuery();
            int revokeResult = (int) revokeLogin.Parameters["@ReturnValue"].Value;

            revokeLogin.Dispose();
            dropRole.Dispose();
            dbConnection.Close();
            dbConnection.Dispose();

            // Finally delete the test user
            Console.WriteLine("Test Teardown, deleting test username " + this._testUserName);
            User user = User.Load(this._testUserName);
            user.Delete();

            Assert.AreEqual( 0, dropMemberResult);
            Assert.AreEqual( 0, revokeResult);
            Assert.AreEqual( 0, dropRoleResult);
        }



        public SqlRoleAddUserTests()
        {
        }

        /// <summary>
        /// Test what happens when you call the execute method without setting parameters
        /// </summary>
        [Ignore]//("Not running on Yukon yet")]
        [TestMethod]
        [ExpectedException(typeof(ApplicationException))]
        public void TestExecuteWithNoParameters()
        {
            Tasks.Sql.Role.AddUser task = new Tasks.Sql.Role.AddUser();
            task.Execute();
        }

        /// <summary>
        /// Check the default properties after object create, and then the property set/get methods
        /// </summary>
        [Ignore]//("Not running on Yukon yet")]
        [TestMethod]
        public void TestProperties()
        {
            Tasks.Sql.Role.AddUser task = new Tasks.Sql.Role.AddUser();
            
            task.DatabaseName = "(dbname)";
            if (task.DatabaseName != "(dbname)")
                Assert.Fail("database name property set/get failed.");

            task.ServerName = "(servername)";
            if (task.ServerName != "(servername)")
                Assert.Fail("server name property set/get failed.");

            task.UserName = "(username)";
            if (task.UserName != "(username)")
                Assert.Fail("user name property set/get failed.");

            task.RoleName = "(rolename)";
            if (task.RoleName != "(rolename)")
                Assert.Fail("role name property set/get failed.");
        }

        /// <summary>
        /// Check adding the temporary user to the temporary role on the local server
        /// </summary>
        [Ignore]//("Not running on Yukon yet")]
        [TestMethod]
        public void TestAddUserToRoleAgainstLocalServer()
        {
            Tasks.Sql.Role.AddUser task = new Tasks.Sql.Role.AddUser();
           
            task.UserName = System.Environment.MachineName + @"\" + this._testUserName;
            task.RoleName = this._testRole;

            task.DatabaseName = "master";

            task.Execute();

            Assert.IsTrue(CheckRoleForUser("(local)"), "Adding user to role on local machine failed");

        }

        /// <summary>
        /// Check adding the temporary user to the temporary role on a named instance
        /// </summary>
        [Ignore]//("Not running on Yukon yet")]
        [TestMethod]
        public void TestAddUserToRoleAgainstNamedServer()
        {
            Tasks.Sql.Role.AddUser task = new Tasks.Sql.Role.AddUser();
           
            task.UserName = System.Environment.MachineName + @"\" + this._testUserName;
            task.RoleName = this._testRole;
            task.ServerName = System.Environment.MachineName;
            task.DatabaseName = "master";

            task.Execute();
            Assert.IsTrue(CheckRoleForUser(System.Environment.MachineName), "Adding user to role on named instance failed");

        }

        /// <summary>
        /// Check adding the temporary user to the temporary role against a bad server name
        /// </summary>
        [Ignore]//("Not running on Yukon yet")]
        [TestMethod]
        public void TestAddUserToRoleAgainstBadServer()
        {
            Guid guid = Guid.NewGuid();
            string instanceName = guid.ToString();
            instanceName = System.Environment.MachineName + @"\" + instanceName.Replace("-", "").Substring(0, 11);
            // Now check the instance doesn't actually exist
            try
            {
                SqlConnection dbConnection = new SqlConnection("Data Source=" + instanceName + ";" +
                                "Initial Catalog=master;" +
                                "Integrated Security=SSPI;" +
                                "Connection Timeout=1");
                dbConnection.Open();
                dbConnection.Close();
                Assert.Fail("Random SQL instance name actually exists on this machine.");
            }
            catch (SqlException)
            {
                // Swallow 'em.
            }

            Tasks.Sql.Role.AddUser task = new Tasks.Sql.Role.AddUser();
             
            try
            {
                
                task.UserName = System.Environment.MachineName + @"\" + this._testUserName;
                task.RoleName = this._testRole;
                task.ServerName = instanceName;
                task.DatabaseName = "master";

                task.ConnectionTimeout = 1;

                task.Execute();
                Assert.Fail("Role adding on non-existant instance did not fail.");

            }
            catch (ApplicationException)
            {
                string lastMessage = ((PseudoBuildEngine) task.BuildEngine).PreviousMessage;
                // Make sure the error message was the instance not found
                //Assert.IsTrue(lastMessage.IndexOf("Error Locating Server/Instance Specified") >= 0);
                Assert.IsTrue(lastMessage.IndexOf("Shared Memory Provider: The system cannot find the file specified.") >= 0);
            }
        }

        /// <summary>
        /// Check what happens if you specify a bad role
        /// </summary>
        [Ignore]//("Not running on Yukon yet")]
        [TestMethod]
        public void TestAddUserToRoleWithBadRole()
        {
            Guid guid = Guid.NewGuid();
            string roleName = guid.ToString();
            roleName = roleName.Replace("-", "").Substring(0, 11);
            // Check the random role name doesn't exist
            SqlConnection dbConnection = new SqlConnection("Data Source=(local);" +
                            "Initial Catalog=master;" +
                            "Integrated Security=SSPI");
            dbConnection.Open();
            SqlCommand checkRole = new SqlCommand("sp_helprole '" + roleName + "'",
                dbConnection);
            try
            {
                checkRole.ExecuteNonQuery();
                Assert.Fail("random bad role check didn't error");

            }
            catch (SqlException)
            {
                // Swallow them
                ;
            }
            finally
            { 
                checkRole.Dispose();
                dbConnection.Close();
                dbConnection.Dispose();
            }

            Tasks.Sql.Role.AddUser task = new Tasks.Sql.Role.AddUser();
           
            task.UserName = System.Environment.MachineName + @"\" + this._testUserName;
            task.RoleName = roleName;
            task.DatabaseName = "master";

            task.ServerName = System.Environment.MachineName;

            try
            {
                task.Execute();
            }
            catch (ApplicationException)
            {
                string lastMessage = ((PseudoBuildEngine) task.BuildEngine).PreviousMessage;
                // Make sure the error message was the correct user does not exist message
                Assert.IsTrue(lastMessage.IndexOf(" role '"+roleName+"' does not exist") >= 0);
            }
        }

        /// <summary>
        /// Check what happens if you specify a bad role
        /// </summary>
        [Ignore]//("Not running on Yukon yet")]
        [TestMethod]
        public void TestAddUserToRoleWithBadUser()
        {
            Guid guid = Guid.NewGuid();
            string userName = guid.ToString();
            userName = userName.Replace("-", "").Substring(0, 11);
            // Check the random role name doesn't exist
            SqlConnection dbConnection = new SqlConnection("Data Source=(local);" +
                            "Initial Catalog=master;" +
                            "Integrated Security=SSPI");
            dbConnection.Open();
            SqlCommand checkUser = new SqlCommand("sp_helpuser '" + 
                System.Environment.MachineName+@"\"+userName + "'",
                dbConnection);

            try
            {
                checkUser.ExecuteNonQuery();
                Assert.Fail("random bad user check didn't error");
            }
            catch (SqlException)
            {
                // Swallow them
                ;
            }
            finally
            {
                checkUser.Dispose();
                dbConnection.Close();
                dbConnection.Dispose();
            }

            Tasks.Sql.Role.AddUser task = new Tasks.Sql.Role.AddUser();
            
            task.UserName = System.Environment.MachineName + @"\" + this._testUserName;
            task.RoleName = this._testRole;
            task.DatabaseName = "master";

            task.ServerName = System.Environment.MachineName;

            try
            {
                task.Execute();
            }

            catch (ApplicationException)
            {
                string lastMessage = ((PseudoBuildEngine) task.BuildEngine).PreviousMessage;
                // Make sure the error message was the correct user does not exist message
                Assert.IsTrue(lastMessage.IndexOf(" role '" + this._testRole + "' does not exist") >= 0);
            }
        }


        /// <summary>
        /// Test what is returned if you try to add a user to a role without having permission
        /// </summary>
        [Ignore]//("Not running on Yukon yet")]
        [TestMethod]
        public void TestAddUserToRoleWithoutPermissions()
        {
            // Swap context to the temporary user created during setup.

            const int LOGON32_LOGON_INTERACTIVE = 2;
            const int LOGON32_PROVIDER_DEFAULT = 0;
            IntPtr userHandle = new IntPtr(0);
            bool returnValue = NativeMethods.LogonUser(this._testUserName,
                System.Environment.MachineName,
                this._testPassword,
                LOGON32_LOGON_INTERACTIVE, LOGON32_PROVIDER_DEFAULT, ref userHandle);

            if (!returnValue)
                throw new Exception("Invalid Username");
            WindowsIdentity newId = new WindowsIdentity(userHandle);
            WindowsImpersonationContext impersonatedUser = newId.Impersonate();
            Console.WriteLine("Now running in context of : " +
                            WindowsIdentity.GetCurrent().Name);
            // Now try it, using the previously constructed task, for neatness
            
            Tasks.Sql.Role.AddUser contextTask = new Tasks.Sql.Role.AddUser();
                
            try
            {
                
                contextTask.UserName = System.Environment.MachineName + @"\"+this._testUserName;
                contextTask.RoleName = this._testRole;
                contextTask.DatabaseName = "master";

                contextTask.Execute();
            }
            catch (ApplicationException)
            {
                string lastMessage = ((PseudoBuildEngine)contextTask.BuildEngine).PreviousMessage;
                // Make sure the error message was the correct no permissions
                Assert.IsTrue(lastMessage.IndexOf("User does not have permission to perform this action.") >= 0);
            }
            finally
            {
                try
                {                    
                    impersonatedUser.Undo();
                    if (userHandle != IntPtr.Zero)
                        NativeMethods.CloseHandle(userHandle);
                }
                catch
                {
                    throw;
                }
            }
        }

        /// <summary>
        /// Loop through the role user table, looking for the test user
        /// </summary>
        /// <param name="serverName">server name to connect to</param>
        /// <returns>true if user is found in role, false if not</returns>
        private bool CheckRoleForUser(string serverName)
        {
            SqlConnection dbConnection = new SqlConnection("Data Source="+serverName+";" +
                            "Initial Catalog=master;" +
                            "Integrated Security=SSPI");
            dbConnection.Open();
            SqlCommand checkRole = new SqlCommand("sp_helprolemember '" + this._testRole + "'",
                dbConnection);
            SqlDataReader roleMembers = checkRole.ExecuteReader();
            bool isMember = false;
            if (roleMembers.HasRows)
            {
                while (roleMembers.Read())    
                {
                    if (roleMembers["MemberName"].ToString() == 
                        (System.Environment.MachineName + @"\" + this._testUserName))
                    {
                        isMember = true;
                    }
                } 
            }
            roleMembers.Close();
            roleMembers.Dispose();
            dbConnection.Close();
            dbConnection.Dispose();
            return (isMember);
        }

        internal class NativeMethods
        {
            [DllImport("advapi32.dll", CharSet = CharSet.Auto)]
            public static extern bool LogonUser(String lpszUserName, String lpszDomain, String lpszPassword, int dwLogonType, int dwLogonProvider, ref IntPtr phToken);
            [DllImport("kernel32.dll", CharSet = CharSet.Auto)]
            public extern static bool CloseHandle(IntPtr handle);

        }
    }
}
